package com.example.excel;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.stereotype.Component;

import java.beans.PropertyDescriptor;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

;

/**
 * TODO description：excel 导出工具类
 *
 * @author 李兵
 * @version v1.0
 * @CreateDate: 2018/11/7 17:22
 */
@Component
public class ExportExceUtil {

    /**
     * @description TODO:
     * @author 李兵
     * @date  2019/6/6 16:37
     * @Param [diskPath:导出文件后硬盘存放的地址, name：文件名称, list：数据集合目前只支持实体类对象集合, titles：第一行展示的标题，suffix：后缀]
     * @Return void
     **/
    public synchronized void exportEntityObjExcel(String diskPath, String name, List<Object> list,String[] titles, String suffix) throws InstantiationException, IllegalAccessException,
            ClassNotFoundException, SQLException, IOException, InterruptedException {
        String fileName = diskPath + name + "-" + DateUtil.getNowDateFormat("yyyy-MM-dd") + "." + suffix;
        //内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(100);
        //工作表对象
        Sheet sheet = null;
        //行对象
        Row nRow = null;
        //列对象
        Cell nCell = null;
        //开始时间
        long startTime = System.currentTimeMillis();
        System.out.println("strat execute time: " + DateUtil.getDateTime());
        //总行号
        int rowNo = 0;
        //页行号
        int pageRowNo = 0;

        for (Object o : list) {
            //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
            if (rowNo % 300000 == 0) {
                System.out.println("Current Sheet:" + rowNo / 300000);
                //建立新的sheet对象
                sheet = wb.createSheet("我的第" + (rowNo / 300000) + "个工作簿");
                //动态指定当前的工作表
                sheet = wb.getSheetAt(rowNo / 300000);
                //每当新建了工作表就将当前工作表的行号重置为0
                pageRowNo = 0;
            }
            rowNo++;
            //新建行对象
            nRow = sheet.createRow(pageRowNo++);

            BeanWrapper srcBean = new BeanWrapperImpl(o);
            PropertyDescriptor[] pds = srcBean.getPropertyDescriptors();
            for (int i = 0; i < pds.length; i++) {
                PropertyDescriptor pd = pds[i];
                Object value;
                if (pd.getPropertyType().equals(Class.class)) {
                    continue;
                }
                if (pd.getPropertyType().equals(Date.class)) {
                    value = srcBean.getPropertyValue(pd.getName());
                    if (ObjectUtils.allNotNull(value)) {
                        value = DateUtil.getDateFormat((Date) value, "yyyy-MM-dd");
                    }
                } else {
                    value = srcBean.getPropertyValue(pd.getName());
                }

                nCell = nRow.createCell(i);
                if (ObjectUtils.allNotNull(value)) {
                    nCell.setCellValue(value.toString());
                } else {
                    nCell.setCellValue("");
                }
            }
            if (rowNo % 10000 == 0) {
                System.out.println("row no: " + rowNo);
            }


        }
        //处理完成时间
        long finishedTime = System.currentTimeMillis();
        System.out.println("finished execute  time: " + (finishedTime - startTime) / 1000 + "m");
        FileOutputStream fOut = new FileOutputStream(fileName);
        wb.write(fOut);
        fOut.flush();        //刷新缓冲区
        fOut.close();
        //写文件时间
        long stopTime = System.currentTimeMillis();
        System.out.println("write xlsx file time: " + (stopTime - startTime) / 1000 + "m");

    }

}
